<%@ Page language="vb" CodeFile="searchDriver.aspx.vb" Inherits="searchDriver_aspx_vb" %>

<%
    rowNumber = 1
    If (Request.QueryString("do")) = Nothing Then 
        subDo = Request.QueryString("subDo")
        '***** Getting the driverID from QueryString *************
        driverID = Request.QueryString("driverID")
        If Len(driverID) > 0 Then 
            driverID = CLng(driverID)
            dbOpen()
            '********* Selecting the bellow info based on the driverID ************
            RecSet = Conn.Execute("Select c.countryCurrency, (Select Count(v.vehicleID) From tblVehicle v Where v.driverID = d.driverID) As vehicleCount, (Select Count(c.claimID) From (tblClaim c INNER JOIN tblVehicle v ON v.vehicleID = c.vehicleID) Where v.driverID = d.driverID) As claimCount, (Select Sum(c.paymentAmount) From (tblClaim c INNER JOIN tblVehicle v ON v.vehicleID = c.vehicleID) Where v.driverID = d.driverID) As claimSum From (tblDriver d INNER JOIN tblCountry c ON c.countryID = d.countryID) Where d.driverID = " & driverID & "")
            If Not RecSet.EOF Then 
                countryCurrency = RecSet.Fields("countryCurrency").Value
                vehicleCount = RecSet.Fields("vehicleCount").Value
                claimCount = RecSet.Fields("claimCount").Value
                claimSum = RecSet.Fields("claimSum").Value
            End IF
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            If IsDBNull(claimSum) Then 
                claimSum = 0
            End IF
        End If
        '******* logo and menu bar ****************
        Over()
        dbOpen()
        RecSet = Conn.Execute("Select clientID, client, countryID From tblClient Order by client")
        If Not RecSet.EOF Then 
            Do Until RecSet.EOF
                intClientID = RecSet.Fields("clientID").Value
                client = RecSet.Fields("client").Value
                countryID = RecSet.Fields("countryID").Value
                strSelectSubJS = strSelectSubJS + "new Array(" & intClientID & "," & countryID & ",""" & client & """),"
                RecSet.MoveNext()
            Loop
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        dbOpen()
        RecSet = Conn.Execute("Select countryID From tblCountry Order by countryID")
        Do Until RecSet.EOF
            countryID = RecSet.Fields("countryID").Value
            strSelectSubJS = strSelectSubJS + "new Array(0," & countryID & ","" ------------------------------------  ""),"
            RecSet.MoveNext()
        Loop
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        strSelectSubJS = Left(strSelectSubJS, Len(strSelectSubJS) - 1)
        strSelectSubHeadline = "Select partner"
        '******** selectSub() is used to allow the user to have the result based on the selection made from both dropdown lists
        selectSub()
        '********** fisrt drop down list ******************
        Response.Write("<table cellpadding='0' cellspacing='0' border='0' width='1000'>")
        Response.Write("<tr>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        Response.Write("<tr><td class='headline' colspan='3'>SEARCH DRIVER<hr class='hrHeadline'></td></tr>")
        Response.Write("<form action='searchDriver.aspx?subDo=search' method='post' name='store_productsSearch'>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Country:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='countryID' id='countryID' onchange=""set_child_listbox(this, document.store_productsSearch.clientID,Product,'Products');"">")
        Response.Write("<option value=''> Select country  ")
        dbOpen()
        RecSet = Conn.Execute("Select countryID, country From tblCountry")
        If Not RecSet.EOF Then 
            Do Until RecSet.EOF
                countryID = RecSet.Fields("countryID").Value
                country = RecSet.Fields("country").Value
                Response.Write("<option value='" & countryID & "'> " & country & " ")
                RecSet.MoveNext()
            Loop
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        '*********** Seconde drop down list ******************
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Lease taker:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='clientID' id='clientID' disabled>")
        Response.Write("<option value=''> - Select lease taker - ")
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("</tr>")
        ''******************* The rest of SERCH DRIVER options ****************************
        Response.Write("<tr><td class='formBold'>Driver:</td><td align='right'><input type='text' name='driver' id='driver' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Driver ID:</td><td align='right'><input type='text' name='driverID' id='driverID' size='40' maxlength='50' onkeyup='re=/(\d*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
        Response.Write("<tr><td class='formBold'>Street:</td><td align='right'><input type='text' name='street' id='street' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Zipcode:</td><td align='right'><input type='text' name='zipcode' id='zipcode' size='40' maxlength='50' onkeyup='re=/(\d*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
        Response.Write("<tr><td class='formBold'>City:</td><td align='right'><input type='text' name='city' id='city' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Areacode:</td><td align='right'><input type='text' name='areacode' id='areacode' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Telephone:</td><td align='right'><input type='text' name='telephone' id='telephone' size='40'  maxlength='50'></td></tr>")
        Response.Write("<tr><td class='formBold'>Email:</td><td align='right'><input type='text' name='email' id='email' size='40' maxlength='50'></td></tr>")
        Response.Write("<tr><td align='right' colspan='2'><input type='submit' value='Search'></td></tr>")
        Response.Write("</form>")
        Response.Write("<tr><td colspan='3'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        '******************** The bellow table appears aftere clicking on EDIT DRIVER ( DRIVER STATISTICS ) **********************
        Response.Write("<td width='100'></td>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        If Len(driverID) > 0 Then 
            Response.Write("<tr><td class='headline' colspan='2'>DRIVER STATISTICS<hr class='hrHeadline'></td></tr>")
            Response.Write("<tr bgcolor='#eeeeee'><td>Number of lease contracts:</td><td align='right'>" & vehicleCount & "</td></tr>")
            Response.Write("<tr><td>Number of claims reported:</td><td align='right'>" & claimCount & "</td></tr>")
            Response.Write("<tr bgcolor='#eeeeee'><td>Total claim cost (" & countryCurrency & "):</td><td align='right'>" & FormatNumber(CDbl(claimSum), 2) & "</td></tr>")
            Response.Write("<tr><td colspan='2'><hr class='hrHeadline'></td></tr>")
        End IF
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td><br></td></tr>")
        Response.Write("<tr>")
        Response.Write("<td valign='top' colspan='3'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='1000'>")
        '************* The belloow info will appear after clicking on SEARCH driver ************************
        '************* The info are taken from the first RecSet and assigned to variables ******************
        Response.Write("<tr><td class='headline' colspan='12'>SEARCH RESULT<hr class='hrHeadline'></td></tr>")
        countryID = Request.Form("countryID")
        intClientID = Request.Form("clientID")
        driver = Request.Form("driver")
        driverID = Request.Form("driverID")
        street = Request.Form("street")
        zipcode = Request.Form("zipcode")
        city = Request.Form("city")
        areacode = Request.Form("areaCode")
        telephone = Request.Form("telephone")
        email = Request.Form("email")
        If Len(intClientID) = 0 Then
            intClientID = Request.QueryString("clientID")
        End If
        If Len(driverID) = 0 Then 
            driverID = Request.QueryString("driverID")
        End IF
        If Len(countryID) > 0 Then 
            sqlCountryID = " And d.countryID = " & countryID & ""
        End IF
        If Len(intClientID) > 0 Then
            sqlClientID = " And d.clientID = " & intClientID & ""
        End If
        If Len(driver) > 0 Then 
            driver = Trim(driver)
            driver = Replace(CStr(driver), "'", "")
            sqlDriver = " And d.driver Like '%" & driver & "%'"
        End IF
        If Len(driverID) > 0 Then 
            sqlDriverID = " And d.driverID = " & driverID & ""
        End IF
        If Len(street) > 0 Then 
            street = Trim(street)
            street = Replace(street, "'", "")
            sqlStreet = " And d.street Like '%" & street & "%'"
        End IF
        If Len(zipcode) > 0 Then 
            sqlZipcode = " And d.zipcode Like '%" & zipcode & "%'"
        End IF
        If Len(city) > 0 Then 
            city = Trim(city)
            city = Replace(CStr(city), "'", "")
            sqlCity = " And d.city Like '%" & city & "%'"
        End IF
        If Len(areacode) > 0 Then 
            areacode = Trim(areacode)
            areacode = Replace(areacode, "'", "")
            sqlAreaCode = " And d.areaCode Like '%" & areacode & "%'"
        End IF
        If Len(telephone) > 0 Then 
            telephone = Trim(telephone)
            telephone = Replace(telephone, "'", "")
            sqlTelephone = " And d.telephone Like '%" & telephone & "%'"
        End IF
        If Len(email) > 0 Then 
            email = Trim(email)
            email = Replace(email, "'", "")
            sqlEmail = " And d.email Like '%" & email & "%'"
        End IF
        If Len(subDo) = 0 Then 
            sqlCountryID = " And d.countryID = 1000"
        End IF
        dbOpen()
        RecSet = Conn.Execute("Select d.driverID, d.driver, d.city, cl.client, c.country From ((tblDriver d INNER JOIN tblClient cl ON cl.clientID = d.clientID) INNER JOIN tblCountry c ON c.countryID = d.countryID) Where d.clientID > 0" & sqlClientID & sqlCountryID & sqlDriverID & sqlDriver & sqlStreet & sqlZipcode & sqlCity & sqlAreaCode & sqlTelephone & sqlEmail & " Order By d.driver")
        If RecSet.EOF Then 
            Response.Write("<tr><td><font color='red'>No customers found.</font></td></tr>")
        Else
            Response.Write("<tr bgcolor='#eeeeee'>")
            Response.Write("<td class='formBold'>Driver</td>")
            Response.Write("<td class='formBold'>Lease taker</td>")
            Response.Write("<td class='formBold'>City</td>")
            Response.Write("<td class='formBold'>Country</td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("</tr>")
            Do Until RecSet.EOF
                driverID = RecSet.Fields("driverID").Value
                driver = RecSet.Fields("driver").Value
                city = RecSet.Fields("city").Value
                client = RecSet.Fields("client").Value
                country = RecSet.Fields("country").Value
                If rowNumber = 0 Then 
                    strGrey = " bgcolor='#eeeeee'"
                    rowNumber = 1
                Else
                    strGrey = ""
                    rowNumber = 0
                End IF
                Response.Write("<tr" & strGrey & ">")
                Response.Write("<td>" & driver & "</td>")
                Response.Write("<td>" & client & "</td>")
                Response.Write("<td>" & city & "</td>")
                Response.Write("<td>" & country & "</td>")
                Response.Write("<td align='right'><a href='editDriver.aspx?driverID=" & driverID & "'>edit driver</a></td>")
                Response.Write("<td align='right'><a href='searchDriver.aspx?subDo=viewVehicles&driverID=" & driverID & "'>view lease contracts</a></td>")
                Response.Write("</tr>")
                RecSet.MoveNext()
            Loop
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<tr><td colspan='12'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td><br></td></tr>")
        '************** The LEASE CONTRACTS table will appear after clicking on view lease contracts *********************
        If subDo = "viewVehicles" Then 
            Response.Write("<tr>")
            Response.Write("<td valign='top' colspan='3'>")
            Response.Write("<table cellpadding='2' cellspacing='2' width='1000'>")
            Response.Write("<tr><td class='headline' colspan='12'>LEASE CONTRACTS<hr class='hrHeadline'></td></tr>")
            dbOpen()
            RecSet = Conn.Execute("Select v.vehicleID, v.regnr, v.vinNumber, v.contractNumber, v.date AS tblVehicleDate, p.product, b.brand, m.model, c.country From ((((tblVehicle v INNER JOIN tblProduct p ON p.productID = v.productID) INNER JOIN tblBrand b ON b.brandID = v.brandID) INNER JOIN tblModel m ON m.modelID = v.modelID) INNER JOIN tblCountry c ON c.countryID = v.countryID) Where v.driverID = " & driverID & " ORDER BY v.vehicleID")
            If RecSet.EOF Then
                '********* if no lease found in the database So display NO LEASE CONTRACTS FOUND ************************
                Response.Write("<tr><td><font color='red'>No lease contracts found.</font></td></tr>")
            Else
                '*************** ELSE display the bellow info ***************
                Response.Write("<tr bgcolor='#eeeeee'>")
                Response.Write("<td class='formBold'>Contract number</td>")
                Response.Write("<td class='formBold'>Licence number</td>")
                Response.Write("<td class='formBold'>VIN-number</td>")
                Response.Write("<td class='formBold'>Car make</td>")
                Response.Write("<td class='formBold'>Product</td>")
                Response.Write("<td class='formBold'>Country</td>")
                Response.Write("<td class='formBold'>First created</td>")
                Response.Write("<td class='formBold'></td>")
                Response.Write("<td class='formBold'></td>")
                Response.Write("</tr>")
                rowNumber = 0
                Do Until RecSet.EOF
                    vehicleID = RecSet.Fields("vehicleID").Value
                    regnr = RecSet.Fields("regnr").Value
                    vinNumber = RecSet.Fields("vinNumber").Value
                    contractNumber = RecSet.Fields("contractNumber").Value
                    tblVehicleDate = RecSet.Fields("tblVehicleDate").Value
                    product = RecSet.Fields("product").Value
                    brand = RecSet.Fields("brand").Value
                    model = RecSet.Fields("model").Value
                    country = RecSet.Fields("country").Value
                    If rowNumber = 0 Then
                        strGrey = ""
                        rowNumber = 1
                    Else
                        strGrey = " bgcolor='#eeeeee'"
                        rowNumber = 0
                    End If
                    Response.Write("<tr" & strGrey & ">")
                    Response.Write("<td>" & contractNumber & "</td>")
                    Response.Write("<td>" & regnr & "</td>")
                    Response.Write("<td>" & vinNumber & "</td>")
                    Response.Write("<td>" & brand & " " & model & "</td>")
                    Response.Write("<td>" & product & "</td>")
                    Response.Write("<td>" & country & "</td>")
                    Response.Write("<td>" & Left(Today(), 10) & "</td>")
                    Response.Write("<td align='right'><a href='changeDriver.aspx?vehicleID=" & vehicleID & "'>change driver</a></td>")
                    Response.Write("<td align='right'><a href='editVehicle.aspx?vehicleID=" & vehicleID & "'>view details</a></td>")
                    Response.Write("</tr>")
                    RecSet.MoveNext()
                Loop
            End If
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            Response.Write("<tr><td colspan='12'><hr class='hrHeadline'></td></tr>")
            Response.Write("</table>")
            Response.Write("</td>")
            Response.Write("</tr>")
        End IF
        Response.Write("</table>")
        Under()
    End IF
%>
